﻿
CREATE PROCEDURE [dbo].[fares_GenereazaDocumentePentruPlata]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@IDPartener bigint,
	@TipOperatiune varchar(10),
	@IDDocumentPlata int,
	@An varchar(4)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    SELECT doc.IDDocument,NrDoc,DataDoc,NrDoc + '/' +CONVERT(varchar(20),DataDoc,104) NrDataDoc ,IDPartener,CodUnitate,Total,TotalValoareTVA,
	Coalesce(Achitat,0) Achitat,COALESCE(AchitatTVA,0) AchitatTVA, 
	Total - Coalesce(Achitat,0) RestPlata,TotalValoareTVA -Coalesce(AchitatTVA,0) RestPlataTVA,  DataScadenta
	FROM [Documente] doc 
	JOIN TipDocument tdoc ON doc.IDTipDocument = tdoc.IDTipDocument and tDoc.SePlateste=1
	LEFT OUTER JOIN
	(SELECT IDDocument, SUM(Valoare) Achitat,SUM(ValoareTVA) AchitatTVA 
	   FROM [DocumentePlataDetaliu] dpd
	   WHERE
	   (@IDDocumentPlata < 0 OR (dpd.IDDocumentPlata <> @IDDocumentPlata)) 
	   GROUP BY IDDocument) pldoc ON pldoc.IDDocument=doc.IDDocument
	WHERE CodUnitate=@CodUnitate  AND IDPartener = @IDPartener AND
	tdoc.TipOperatie like '%'+@TipOperatiune+'%'
	UNION
	SELECT NULL, '',GETDATE(),'AVANS',@IDPartener,@CodUnitate,0,0,0,0,0,0,GETDATE() 
	FROM FaresSetup WHERE CodUnitate=@CodUnitate 
	ORDER BY DataDoc DESC
	
END